<?php
/**
* @software         [JiJinCMS]
* @author           [上海集锦信息科技有限公司]
* @website          [http://www.jijinweb.com]
* @email            [sunny@jijinweb.com]
**/
namespace app\admin\model;
use think\Model;
use think\facade\Db;
class TableFieldModel extends Model{
    protected $name = 'table_field';
    protected $pk = 'field_id';
    /**
     * 根据字段类型，增加对应的字段到相应表里面
     * @param type $field_type 字段类型
     * @param type $field 相关配置
     * $field = array(
     *      'tablename' 表名(完整表名)
     *      'fieldname' 字段名
     *      'maxlength' 最大长度
     *      'minlength' 最小值
     *      'defaultvalue' 默认值
     *      'minnumber' 是否正整数 和整数 1为正整数，-1是为整数
     *      'decimaldigits' 小数位数
     * )
     */
    public function addFieldSql($field_type, $field) {
        //表名
        $tablename = $field['tablename'];
        //字段名
        $fieldname = $field['fieldname'];
        //长度
        $fieldlen = $field['fieldlen'];
        switch ($field_type) {            
            case "varchar":
                if (!$fieldlen) {
                    $fieldlen = 255;
                }
                $fieldlen = min($fieldlen, 255);
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` VARCHAR( {$fieldlen} ) DEFAULT ''";
                Db::execute($sql);
                break;
            case "tinyint":
                if (!$fieldlen) {
                    $fieldlen = 3;
                }
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` TINYINT( {$fieldlen} ) UNSIGNED NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "smallint":
                $minnumber = intval($minnumber);
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` SMALLINT( {$fieldlen} ) UNSIGNED NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "mediumint":
                $minnumber = intval($minnumber);
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` INT ( {$fieldlen} ) UNSIGNED NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "int":
                $minnumber = intval($minnumber);
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` INT ( {$fieldlen} ) UNSIGNED NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "mediumtext":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` MEDIUMTEXT";
                Db::execute($sql);
                break;
            case "text":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` TEXT";
                Db::execute($sql);
                break;
            case "date":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` DATE DEFAULT ''";
                Db::execute($sql);
                break;
            case "datetime":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP";
                Db::execute($sql);
                break;
            case "timestamp":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP";
                Db::execute($sql);
                break;
            case "double":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` DOUBLE NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "float":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` FLOAT( {$fieldlen} ) NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "bigint":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}` BIGINT( {$fieldlen} ) NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "longtext":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}`  LONGTEXT";
                Db::execute($sql);
                break;
            case "char":
                $sql = "ALTER TABLE `{$tablename}` ADD `{$fieldname}`  CHAR( {$fieldlen} ) DEFAULT ''";
                Db::execute($sql);
                break;
            default:
                return false;
                break;
        }
        return true;
    }  
    /**
     * 根据模型ID，返回表名
     * @param type $table_id
     * @param type $table_id
     * @return string
     */
    public function getTbName($table_id, $issystem = 1) {
        //表名获取
        $table_name = Db::name("table")->where("table_id",$table_id)->value("table_name");
        $table_name="cms_".$table_name;
        //完整表名获取 判断主表 还是副表
        $table_name = $issystem ? $table_name : $table_name . "_data";
        return $table_name;
    }    
    /**
     * 获取表字段
     * $table 不带表前缀
     */
    public function get_fields($table) {
        $fields = array();
        $table = config('database.connections.mysql.prefix') . $table;
        $data = Db::query("SHOW COLUMNS FROM $table");
        foreach ($data as $v) {
            $v=array_change_key_case($v,CASE_LOWER );//键名统一转小写
            $fields[$v['field']] = $v['type'];
        }
        return $fields;
    }    
    /**
     * 检查字段是否存在
     * $table 不带表前缀
     */
    public function field_exists($table, $field) {        
        $fields = $this->get_fields($table);
        return array_key_exists($field, $fields);
    }  
    /**
     * 执行数据库表结构更改
     * @param type $field_type 字段类型
     * @param type $field 相关配置
     * $field = array(
     *      'tablename' 表名(完整表名)
     *      'newfilename' 新字段名
     *      'oldfilename' 原字段名
     *      'maxlength' 最大长度
     *      'minlength' 最小值
     *      'defaultvalue' 默认值
     *      'minnumber' 是否正整数 和整数 1为正整数，-1是为整数
     *      'decimaldigits' 小数位数
     * )
     */
    public function editFieldSql($field_type, $field) {      
        //表名
        $tablename = $field['tablename'];
        //原字段名
        $oldfilename = $field['oldfieldname'];
        //新字段名
        $newfilename = $field['newfieldname'] ? $field['newfieldname'] : $oldfilename;
        //长度
        $fieldlen = $field['fieldlen'];
        if (empty($tablename) || empty($newfilename)) {
            $this->error("表名或者字段名不能为空！");
            return false;
        }

        switch ($field_type) {
            case 'varchar':
                //最大值
                if (!$fieldlen) {
                    $fieldlen = 255;
                }
                $fieldlen = min($fieldlen, 255);
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` VARCHAR( {$fieldlen} ) DEFAULT ''";
                Db::execute($sql);
                break;
            case 'tinyint':
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` TINYINT( {$fieldlen} ) UNSIGNED NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case 'smallint':
                $minnumber = intval($minnumber);
                $defaultvalue = intval($defaultvalue);
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` SMALLINT ( {$fieldlen} ) UNSIGNED  NOT NULL DEFAULT '{$defaultvalue}' DEFAULT 0";
                Db::execute($sql);
                break;
            case 'mediumint':
                $minnumber = intval($minnumber);
                $defaultvalue = intval($defaultvalue);
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` MEDIUMINT ( {$fieldlen} ) UNSIGNED  NOT NULL DEFAULT '{$defaultvalue}' DEFAULT 0";
                Db::execute($sql);
                break;
            case 'int':
                $minnumber = intval($minnumber);
                $defaultvalue = intval($defaultvalue);
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` INT ( {$fieldlen} ) NOT NULL DEFAULT '{$defaultvalue}' DEFAULT 0";
                Db::execute($sql);
                break;
            case 'mediumtext':
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` MEDIUMTEXT";
                Db::execute($sql);
                break;
            case 'text':
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` TEXT";
                Db::execute($sql);
                break;
            case 'date':
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` DATE DEFAULT ''";
                Db::execute($sql);
                break;
            case 'datetime':
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP";
                Db::execute($sql);
                break;
            case 'timestamp':
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP";
                Db::execute($sql);
                break;
            case "double":
                $defaultvalue = intval($defaultvalue);
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` DOUBLE NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "float":
                $defaultvalue = intval($defaultvalue);
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}` FLOAT(" . $minnumber . ") NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "bigint":
                $defaultvalue = intval($defaultvalue);
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}`  BIGINT NOT NULL DEFAULT 0";
                Db::execute($sql);
                break;
            case "longtext":
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}`  LONGTEXT";
                Db::execute($sql);
                break;
            case "char":
                $sql = "ALTER TABLE `{$tablename}` CHANGE `{$oldfilename}` `{$newfilename}`  CHAR(" . $fieldlen . ") DEFAULT ''";
                Db::execute($sql);
                break;
            default:
                $this->error("不存在相关字段信息");
                return false;
                break;
        }
        return true;
    }   
        /**
     * 根据字段类型，删除对应的字段到相应表里面
     * @param type $filename 字段名称
     * @param type $tablename 完整表名
     */
    public function deleteFieldSql($field, $table_name) {
        if (!table_exists($table_name)) {
            $this->error("该表表不存在!");
        }
        $all_table_name=config('database.connections.mysql.prefix').$table_name;
        $sql = "ALTER TABLE `{$all_table_name}` DROP `{$field}`;";
        Db::execute($sql);
    }  
    //morevalue类型字段，将数据处理存入defaultvalue字段里
    public function detailMorevalueDefaultval($data){
        $defaultvalue="";
        $morevalue_name=$data["morevalue_name"];
        $morevalue_type=$data["morevalue_type"];
        $morevalue_defaultvalue=$data["morevalue_defaultvalue"];
        $morevalue_width=$data["morevalue_width"];
        foreach($morevalue_name as $key=>$val){
            if($defaultvalue){
                $defaultvalue.="{{RsSplit}}".$morevalue_name[$key]."{{FieldSplit}}".$morevalue_type[$key]."{{FieldSplit}}".$morevalue_defaultvalue[$key]."{{FieldSplit}}".$morevalue_width[$key];
            }else{
                $defaultvalue =$morevalue_name[$key]."{{FieldSplit}}".$morevalue_type[$key]."{{FieldSplit}}".$morevalue_defaultvalue[$key]."{{FieldSplit}}".$morevalue_width[$key];
            }
        }
        return $defaultvalue;
    }    
}
